﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GAMS.MODEL;
using KPIS.DBM;
using System.Configuration;
using System.Data;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_PAYMENT_Gernal
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_ACM_PAYMENT_Gernal> GetListForExpenseGroup()
        {
            DataRowCollection drc = null;
            IList<M_ACM_PAYMENT_Gernal> infoList = new List<M_ACM_PAYMENT_Gernal>();
            try
            {
                dbManager.Open();

                string strSQL = " SELECT bgm_exp_grp_seq,bgm_exp_grp_code,bgm_exp_grp_name,concat(coalesce(bgm_exp_grp_code, ''), ' : ', coalesce(bgm_exp_grp_name, '')) as exp_grp_name "
                              + " FROM bgm_mas_expense_group "
                              + " WHERE record_status not like 'D' "
                              + " ORDER BY bgm_exp_grp_code "; 
                
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_PAYMENT_Gernal info = new M_ACM_PAYMENT_Gernal();
                        info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.exp_grp_name = drc[i]["exp_grp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_PAYMENT_Gernal> GetListForUpperExpense(Nullable<int> bgm_exp_grp_seq)
        {
            DataRowCollection drc = null;
            IList<M_ACM_PAYMENT_Gernal> infoList = new List<M_ACM_PAYMENT_Gernal>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string strSQL = " SELECT bgm_exp_grp_seq,bgm_exp_seq,bgm_exp_code,bgm_exp_name,concat(coalesce(bgm_exp_code,''),' : ' ,coalesce(bgm_exp_name,'')) as exp_name  ,upper_bgm_exp_seq,expe.account_seq  ,acc.account_name, "
                               +" acc.account_code, concat(coalesce(acc.account_code, ''), ' : ', coalesce(acc.account_name, '')) as acc_name "
                               +" FROM acm_mas_account AS acc Right Join bgm_mas_expense AS expe ON acc.account_seq = expe.account_seq "
                               + " WHERE expe.record_status not like 'D' and bgm_exp_grp_seq = @bgm_exp_grp_seq "
                               + " ORDER BY bgm_exp_code ";
                    
                               //  " SELECT bgm_exp_grp_seq,bgm_exp_seq,bgm_exp_code,bgm_exp_name,concat(coalesce(bgm_exp_code,''),' : ' ,coalesce(bgm_exp_name,'')) as exp_name "
                               //+ " ,upper_bgm_exp_seq,account_seq "
                               //+ " FROM bgm_mas_expense "
                               //+ " WHERE record_status not like 'D' and bgm_exp_grp_seq = @bgm_exp_grp_seq "
                               //+ " ORDER BY bgm_exp_code ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_PAYMENT_Gernal info = new M_ACM_PAYMENT_Gernal();
                        info.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["upper_bgm_exp_seq"]);
                        info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.bgm_exp_seq = Convert.ToInt32(drc[i]["bgm_exp_seq"]);
                        info.bgm_exp_name = drc[i]["exp_name"].ToString();
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"].ToString());
                        info.account_code = drc[i]["account_code"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_PAYMENT_Gernal> GetListForExpense(Nullable<int> bgm_exp_grp_seq, Nullable<int> upper_bgm_exp_seq)
        {
            DataRowCollection drc = null;
            IList<M_ACM_PAYMENT_Gernal> infoList = new List<M_ACM_PAYMENT_Gernal>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(1, "@upper_bgm_exp_seq", upper_bgm_exp_seq);

                string strSQL = " SELECT bgm_exp_grp_seq,bgm_exp_seq,bgm_exp_code,bgm_exp_name,concat(coalesce(bgm_exp_code,''),' : ' ,coalesce(bgm_exp_name,'')) as exp_name "
                               + " ,upper_bgm_exp_seq ,account_seq "
                               + " FROM bgm_mas_expense "
                               + " WHERE record_status not like 'D' and bgm_exp_grp_seq = @bgm_exp_grp_seq "
                               + " and upper_bgm_exp_seq = @upper_bgm_exp_seq "
                               + " ORDER BY bgm_exp_code ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_ACM_PAYMENT_Gernal info = new M_ACM_PAYMENT_Gernal();
                        info.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["upper_bgm_exp_seq"]);
                        info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.bgm_exp_seq = Convert.ToInt32(drc[i]["bgm_exp_seq"]);
                        info.account_seq = Convert.ToInt32(drc[i]["account_seq"]);
                        info.bgm_exp_name = drc[i]["exp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
      }

     //*********************************Before Change **********************************************//
     public IList<M_ACM_PAYMENT_Gernal> GetListForGrid(Nullable<Int32> bgm_exp_seq)
     {
     DataRowCollection drc = null;
     IList<M_ACM_PAYMENT_Gernal> infoList = new List<M_ACM_PAYMENT_Gernal>();
     try
     {
         dbManager.Open();
         dbManager.CreateParameters(1);
         dbManager.AddParameters(0, "@bgm_exp_seq", bgm_exp_seq);

         string strSQL = " SELECT  bme.bgm_exp_seq,bme.bgm_exp_code,bme.bgm_exp_name,bme.bgm_exp_descr,bme.upper_bgm_exp_seq,bme.bgm_exp_grp_seq,bme.account_seq,acc.account_code "
                        + " , concat(coalesce(bgm_exp_grp_code, ''), ' : ', coalesce(bgm_exp_grp_name, '')) as exp_grp_name ,concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name,acc.account_name "
                        + " ,concat(coalesce(bme.bgm_exp_code, ''), ' : ', coalesce(bme.bgm_exp_name, '')) as bgm_name "
                        + " FROM bgm_mas_expense bme "
                        + " LEFT JOIN bgm_mas_expense_group bmeg ON bme.upper_bgm_exp_seq = bmeg.bgm_exp_grp_seq "
                        + " LEFT JOIN acm_mas_account  acc ON bme.account_seq = acc.account_seq "
                        + " AND acc.record_status = 'U' LEFT JOIN acm_mas_account_version acc_ver "
                        + " ON acc.version_seq = acc_ver.version_seq AND acc_ver.record_status = 'U' AND acc_ver.use_flag = 1 "
                        + " WHERE (bme.record_status not like 'D')  and (bme.upper_bgm_exp_seq=@bgm_exp_seq) "
                        + " ORDER BY bme.bgm_exp_code ";

                /* strSQL = " SELECT  bme.bgm_exp_seq,bme.bgm_exp_code,bme.bgm_exp_name,bme.bgm_exp_descr,bme.upper_bgm_exp_seq,bme.bgm_exp_grp_seq,bme.account_seq,acc.account_code "
                        + " , concat(coalesce(bgm_exp_grp_code, ''), ' : ', coalesce(bgm_exp_grp_name, '')) as exp_grp_name ,concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name,acc.account_name "
                        + " FROM bgm_mas_expense bme "
                        + " LEFT JOIN bgm_mas_expense_group bmeg ON bme.upper_bgm_exp_seq = bmeg.bgm_exp_grp_seq "
                        + " LEFT JOIN acm_mas_account  acc ON bme.account_seq = acc.account_seq "
                        + " AND acc.record_status = 1 LEFT JOIN acm_mas_account_version acc_ver "
                        + " ON acc.version_seq = acc_ver.version_seq AND acc_ver.record_status = 1 AND acc_ver.use_flag = 1 "
                        + " WHERE bme.record_status not like 'D'"
                        + " AND (bme.upper_bgm_exp_seq = @bgm_exp_seq  "
                        + " Or bme.bgm_exp_seq = @bgm_exp_seq)"
                        + " ORDER BY bme.bgm_exp_code ";   Before Train*/

         drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
         if (drc.Count > 0)
         {
             for (int i = 0; i < drc.Count; i++)
             {
                 M_ACM_PAYMENT_Gernal info = new M_ACM_PAYMENT_Gernal();
                 info.bgm_exp_seq = Convert.ToInt32(drc[i]["bgm_exp_seq"]);
                 info.bgm_exp_code = drc[i]["bgm_exp_code"].ToString();
                 info.bgm_exp_name = drc[i]["bgm_name"].ToString();
                 info.bgm_exp_descr = drc[i]["bgm_exp_descr"].ToString();
                 info.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["upper_bgm_exp_seq"]);
                 info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                 info.exp_grp_name = drc[i]["exp_grp_name"].ToString();
                 info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                 info.account_code = drc[i]["account_code"].ToString();
                 info.account_name = drc[i]["acc_name"].ToString();
                 infoList.Add(info);
             }
         }
     }
     catch (Exception ex)
     {
         throw (ex);
     }
     finally
     {
         dbManager.Dispose();
     }
     return infoList;
     }

     //public IList<M_ACM_PAYMENT_Gernal> GetListForExpense()
     //{
     //    DataRowCollection drc = null;
     //    IList<M_ACM_PAYMENT_Gernal> infoList = new List<M_ACM_PAYMENT_Gernal>();
     //    try
     //    {
     //        dbManager.Open();

     //        string strSQL =
     //            //"SELECT '' bgm_exp_grp_seq as bgm_exp_seq, bgm_exp_grp_code as bgm_exp_code"
     //            //+ ", upper_bgm_exp_grp_seq as upper_bgm_exp_seq, bgm_exp_grp_name as bgm_exp_name"
     //            //+ " ,concat(coalesce(bgm_exp_grp_code, ''), ' : ', coalesce(bgm_exp_grp_name, '')) as exp_name"
     //            //+ ", null as account_seq"
     //            //+ " FROM bgm_mas_expense_group"
     //            //+ " WHERE record_status not like 'D'"

     //            //+ " UNION ALL"

     //            " SELECT bgm_exp_seq, bgm_exp_code"
     //            + " , upper_bgm_exp_seq, bgm_exp_name"
     //            + " , concat(coalesce(bgm_exp_code, ''), ' : ', coalesce(bgm_exp_name, '')) as exp_name"
     //            + " , account_seq"
     //            + " FROM bgm_mas_expense "
     //            + " WHERE record_status not like 'D' "
     //            + " ORDER BY bgm_exp_code ";

     //        drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
     //        if (drc.Count > 0)
     //        {
     //            for (int i = 0; i < drc.Count; i++)
     //            {
     //                M_ACM_PAYMENT_Gernal info = new M_ACM_PAYMENT_Gernal();
     //                info.bgm_exp_seq = Convert.ToInt32(drc[i]["bgm_exp_seq"]);
     //                info.upper_bgm_exp_seq = cn.ConvertNullInt(drc[i]["upper_bgm_exp_seq"]);
     //                info.bgm_exp_code = drc[i]["bgm_exp_code"].ToString();
     //                info.bgm_exp_name = drc[i]["exp_name"].ToString();
     //                //  info.bgm_exp_descr = drc[i]["bgm_exp_descr"].ToString();
     //                //info.exp_grp_name = drc[i]["exp_name"].ToString();
     //                info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
     //                infoList.Add(info);
     //            }
     //        }
     //    }
     //    catch (Exception ex)
     //    {
     //        throw (ex);
     //    }
     //    finally
     //    {
     //        dbManager.Dispose();
     //    }
     //    return infoList;
     //}

        public M_ACM_PAYMENT_Gernal GetListFillBySeq(Nullable<int> bgm_exp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_ACM_PAYMENT_Gernal info = new M_ACM_PAYMENT_Gernal();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_exp_seq", bgm_exp_seq);

                strSQL =      " SELECT  bme.bgm_exp_seq,bme.bgm_exp_code,bme.bgm_exp_name,bme.bgm_exp_descr,bme.upper_bgm_exp_seq,bme.bgm_exp_grp_seq,bme.account_seq ,acc.account_code"
                              + " , concat(coalesce(bgm_exp_grp_code, ''), ' : ', coalesce(bgm_exp_grp_name, '')) as exp_grp_name ,concat(coalesce(account_code, ''), ' : ', coalesce(account_name, '')) as acc_name ,acc.account_name"
                              + " FROM bgm_mas_expense bme "
                              + " LEFT JOIN bgm_mas_expense_group bmeg ON bme.upper_bgm_exp_seq = bmeg.bgm_exp_grp_seq "
                              + " LEFT JOIN acm_mas_account  acc ON bme.account_seq = acc.account_seq "
                              + " AND acc.record_status = 'U' LEFT JOIN acm_mas_account_version acc_ver "
                              + " ON acc.version_seq = acc_ver.version_seq AND acc_ver.record_status = 'U' AND acc_ver.use_flag = 1 "
                              + " WHERE bme.record_status not like 'D' "
                              + " and bme.bgm_exp_seq = @bgm_exp_seq "
                              + " ORDER BY bgm_exp_code";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_exp_seq = Convert.ToInt32(drc[0]["bgm_exp_seq"]);
                    info.bgm_exp_code = drc[0]["bgm_exp_code"].ToString();
                    info.bgm_exp_name = drc[0]["bgm_exp_name"].ToString();
                    info.bgm_exp_descr = drc[0]["bgm_exp_descr"].ToString();
                    info.upper_bgm_exp_seq = cn.ConvertNullInt(drc[0]["upper_bgm_exp_seq"]);
                    info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"].ToString());
                    info.exp_grp_name = drc[0]["exp_grp_name"].ToString();
                    info.account_seq = cn.ConvertNullInt(drc[0]["account_seq"]);
                    info.account_code = drc[0]["account_code"].ToString();
                    info.account_name = drc[0]["account_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Update(M_ACM_PAYMENT_Gernal info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@account_seq", info.account_seq);
                dbManager.AddParameters(1, "@bgm_exp_seq", info.bgm_exp_seq);
                //dbManager.AddParameters(2, "@updated_by", info.updated_by);
                //dbManager.AddParameters(3, "@updated_when", info.updated_when);


                strSQL =  "   UPDATE bgm_mas_expense "
                        + "   SET account_seq = @account_seq "
                        + "   WHERE bgm_exp_seq = @bgm_exp_seq ";


                //+ " , updated_by = @updated_by "
                //        + " , updated_when = @updated_when "

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Report_PAYMENT_Gernal(string bgm_exp_grp_seq,string pFilter)
        {
            string strSQL="";
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                if (pFilter == "I")
                {
                    strSQL = "  SELECT  bme.bgm_exp_seq,bme.bgm_exp_code,bme.bgm_exp_name,bme.bgm_exp_descr,bme.upper_bgm_exp_seq,bme.bgm_exp_grp_seq,bme.account_seq,acc.account_code "
                             + " ,acc.account_name ,bmeg.bgm_exp_grp_code,bmeg.bgm_exp_grp_name "
                             + " FROM bgm_mas_expense bme "
                             + " LEFT JOIN bgm_mas_expense_group bmeg ON bme.bgm_exp_grp_seq = bmeg.bgm_exp_grp_seq "
                             + " LEFT JOIN acm_mas_account  acc ON bme.account_seq = acc.account_seq "
                             + " AND acc.record_status = 'U' "
                             + " WHERE bme.record_status not like 'D' "
                             + " AND bme.bgm_exp_grp_seq  = @bgm_exp_grp_seq "
                             + " ORDER BY bme.bgm_exp_code ";
                   // dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                }
                else if (pFilter == "A")
                {
                    strSQL = "  SELECT  bme.bgm_exp_seq,bme.bgm_exp_code,bme.bgm_exp_name,bme.bgm_exp_descr,bme.upper_bgm_exp_seq,bme.bgm_exp_grp_seq,bme.account_seq,acc.account_code "
                             + " ,acc.account_name ,bmeg.bgm_exp_grp_code,bmeg.bgm_exp_grp_name "
                             + " FROM bgm_mas_expense bme "
                             + " LEFT JOIN bgm_mas_expense_group bmeg ON bme.bgm_exp_grp_seq = bmeg.bgm_exp_grp_seq "
                             + " LEFT JOIN acm_mas_account  acc ON bme.account_seq = acc.account_seq "
                             + " AND acc.record_status = 'U' "
                             + " WHERE bme.record_status not like 'D' "
                             + " ORDER BY bme.bgm_exp_code ";
                   // dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }


    }
}
